/* Datastream.do (STATA)
	Construct Datastream data.
	by Ralph Koijen & Motohiro Yogo */

#delimit ;
clear all;
set more off;
set type double;

cap log close;
log using Datastream, replace;

/* Define local and global variables */

local directory = "../../Data/Datastream";
local directory_nonpublic = "../../Data_nonpublic/Datastream";

do global;


/* Step 1: Construct MSCI returns */

/* Load country names */

import excel using "`directory_nonpublic'/MSCI.xlsm",
	sheet("Legend") first clear;

/* Fix variables */

replace CodeUS = subinstr(CodeUS,"$(RI)","RI",1);
replace CodeLC = subinstr(CodeLC,"(RI)","RI",1);

/* Save data */

tempfile Legend;
save `Legend';

/* Load world returns */

import excel using "`directory_nonpublic'/MSCI.xlsm",
	sheet("World Return US") cellra(A2) first clear;

/* Rename variables */

rename Code Date;

/* Construct returns */

sort Date;

gen retMSWRLDRI = ln(MSWRLDRI/MSWRLDRI[_n-1]);

drop MS*;

/* Save data */

tempfile WorldReturn;
save `WorldReturn';

/* Load returns in US$ */

import excel using "`directory_nonpublic'/MSCI.xlsm",
	sheet("Total Return US") cellra(A2) first clear;

/* Rename variables */

rename Code Date;

foreach var of varlist MS* {;
	rename `var' MS`var';
};

/* Reshape in long format */

reshape long MS, i(Date) j(CodeUS) string;

/* Merge country names */

merge m:1 CodeUS using `Legend',
	keepusing(Name)
	nogen;

drop CodeUS;

/* Save data */

tempfile ReturnUS;
save `ReturnUS';

/* Load returns in local currency */

import excel using "`directory_nonpublic'/MSCI.xlsm",
	sheet("Total Return LC") cellra(A2) first clear;

/* Rename variables */

rename Code Date;

foreach var of varlist MS* {;
	rename `var' MSL`var';
};

/* Reshape in long format */

reshape long MSL, i(Date) j(CodeLC) string;

/* Merge country names */

merge m:1 CodeLC using `Legend',
	keepusing(Name)
	nogen;

drop CodeLC;

/* Merge world returns */

merge m:1 Date using `WorldReturn',
	nogen;

/* Merge returns in US$ */

merge 1:1 Date Name using `ReturnUS',
	nogen;

/* Construct variables */

gen int year = year(Date);

replace Date = mofd(Date);

/* Construct returns */

sort Name Date;

foreach var of varlist MS MSL {; 
	by Name: gen ret`var' = ln(`var'/`var'[_n-1]);
};

drop MS*;

/* Construct beta and volatility */

gen beta = .;
gen vol = .;

qui forval d = `=tm(${year_min}m12)'(12)`=tm(${year_max}m12)' {;

	/* Construct beta */

	egen cov = corr(retMS retMSWRLDRI) if inrange(Date,`=`d'-59',`d'), covariance by(Name);
	egen var = var(retMSWRLDRI) if inrange(Date,`=`d'-59',`d'), by(Name);

	replace beta = cov/var if Date==`d';

	/* Construct volatility */

	egen sd = sd(retMS) if inrange(Date,`=`d'-11',`d'), by(Name);

	replace vol = sqrt(12)*sd if Date==`d';

	drop cov var sd;
};

drop retMSWRLDRI;

/* Construct annual returns */

foreach var of varlist retMS retMSL {;
	egen ret = total(`var'), missing by(year Name);

	replace `var' = ret;

	drop ret;
};

/* Keep month end */

keep if month(dofm(Date))==12;

drop Date;

/* Save data */

tempfile Return;
save `Return';


/* Step 2: Construct interest rates */

/* Load country names */

import excel using "`directory'/Interest Rates.xlsm",
	sheet("Legend") first clear;

/* Fix variables */

replace Code3M = subinstr(Code3M,".","",.);

/* Save data */

tempfile Legend;
save `Legend';

/* Load 10-year benchmark government yields */

import excel using "`directory'/Interest Rates.xlsm",
	sheet("10Y Government") cellra(A2) first clear;

/* Rename variables */

rename Code Date;

foreach var of varlist TR* {;
	rename `var' IR10Y`var';
};

/* Fix variables */

replace Date = mofd(Date);

/* Reshape in long format */

reshape long IR10Y, i(Date) j(Code10Y) string;

/* Merge country names */

merge m:1 Code10Y using `Legend',
	keepusing(Name)
	nogen keep(match);

drop Code10Y;

/* Save data */

tempfile Government;
save `Government';

/* Load 3-month interbank rates */

import excel using "`directory'/Interest Rates.xlsm",
	sheet("3M Interbank") cellra(A2) first clear;

/* Rename variables */

rename Code Date;

foreach var of varlist AGI60L-ECEUR3M {;
	rename `var' IR3M`var';
};

/* Fix variables */

replace Date = mofd(Date);

/* Reshape in long format */

reshape long IR3M, i(Date) j(Code3M) string;

/* Merge country names */

merge m:1 Code3M using `Legend',
	keepusing(Name)
	nogen keep(match);

drop Code3M;

/* Merge 10-year benchmark government yields */

merge 1:1 Date Name using `Government',
	nogen;

/* Merge country code */

merge m:1 Name using Countries,
	keepusing(country Yeuro)
	nogen keep(master match);

/* Convert interest rates to decimal */

foreach var of varlist IR3M IR10Y {;
	replace `var' = `var'/100;
};

/* Construct variables */

gen int year = year(dofm(Date));

/* Construct dummy for euro area and Denmark */

gen byte Ieuro = Name=="Euro" | year>=Yeuro | country=="DNK";

drop Yeuro;

/* Common 3-month interbank rate in the euro area */

sort Date Ieuro country;
by Date Ieuro: replace IR3M = IR3M[1] if Ieuro & Name[1]=="Euro";

drop if Name=="Euro";

drop Ieuro;

/* Construct dummy for USD countries */

gen byte Iusd = inlist(country,"USA","HKG");

/* Common 3-month interbank rate for USD countries */

sort Date Iusd country;
by Date Iusd: replace IR3M = IR3M[_N] if Iusd & country[_N]=="USA";

drop Iusd;

/* Construct annual returns */

gen retIR3M = 0;

sort country Date;

forval i = 3(3)12 {;
	by country: replace retIR3M = retIR3M+ln(1+IR3M[_n-`i']/4);
};

/* Keep month end */

keep if month(dofm(Date))==12;

drop Date;


/* Step 3: Merge MSCI returns and interest rates */

merge 1:1 year Name using `Return',
	nogen keep(match);

drop Name;

/* Sample criteria */

keep if inrange(year,$year_min,$year_max+1);

/* Label variables */

order year country;

label var year		"year";

label var IR3M		"3-month interbank rate";
label var IR10Y		"10-year benchmark government yield";
label var retIR3M	"Interest rate (LCU, continuously compounded)";
label var retMS		"Stock return (US$, continuously compounded)";
label var retMSL	"Stock return (LCU, continuously compounded)";
label var beta		"Market beta";
label var vol		"Volatility";

/* Save data */

sort year country;

save Datastream, replace;

log close;
